package praktikum4GastKomponente.Verwalter;
import java.sql.*;

import praktikum4GastKomponente.Datentyp.DGastEMail;
import praktikum4GastKomponente.Datentyp.DGastIstStammkunde;
import praktikum4GastKomponente.Datentyp.DGastName;
import praktikum4GastKomponente.Datentyp.DGastNr;
import praktikum4GastKomponente.Entitaeten.EGast;

public class VGastServices{

	private static String INSERT = "INSERT INTO ";
	private static String UPDATE = "UPDATE ";
	private static String VALUESSTART = " VALUES (";
	private static String VALUESMID = "', '";
	private static String VALUESEND = "')";
	
	public static void main(String[] args) throws ClassNotFoundException {
		
		try {
		     
		      Statement stmt = statement();
			  stmt.executeQuery("DELETE FROM GAST");
			  stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
	public static void markiereGastAlsStammkunden(DGastNr nr) {
		
		Statement stmt = statement();
		
		String querry = UPDATE + "GAST" + " SET" + " DGASTISTSTAMMGAST = '" + "TRUE" + "' WHERE " + "DGASTNR = " + nr.getNr();
		
		try {
			stmt.executeQuery(querry);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void erzeugeGast(EGast eGast){
		
		Statement stmt = statement();
		
		String querry = INSERT + "GAST" + VALUESSTART + eGast.getNr().getNr() + 
				", '" + eGast.getName().getName() + VALUESMID + eGast.getEMail().getEMail() + VALUESMID + eGast.getIstStammkunde().getIstStammkunde() + VALUESEND;
		
		System.out.println(querry);
		try {
			stmt.executeQuery(querry);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static EGast sucheGastNachName(DGastName name) {
		
		
		String querry = "SELECT * FROM GAST WHERE DGASTNAME = '"+ name.getName() +"'";
		System.out.println(querry);
		EGast eGast;
	
		Statement stmt = statement();
		
		try {
			ResultSet rs = stmt.executeQuery(querry);
			rs.next();
			eGast = new EGast(new DGastNr(rs.getInt("DGASTNR")), new DGastName(rs.getString("DGASTNAME")), 
					new DGastEMail(rs.getString("DGASTEMAIL")), new DGastIstStammkunde(rs.getString("DGASTISTSTAMMGAST")));
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new ExceptionInInitializerError("Conection Faild");
		}
				
		
		return eGast;
	}
	
	public static Statement statement(){
		
		try {		
		Class.forName("oracle.jdbc.driver.OracleDriver");
	    String url = "jdbc:oracle:thin:@oracle.informatik.haw-hamburg.de:1521:inf09";
	    Connection con = DriverManager.getConnection(url, praktikum4GastKomponente.Connection.ConnectionDATA.ACC,  praktikum4GastKomponente.Connection.ConnectionDATA.PASS);
	    return con.createStatement();
		} catch (SQLException e) {
			throw new ExceptionInInitializerError("Conection Faild");
		}
		catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError("Conection Faild");
		}
	}
	
	public static void init(){
		try {
		      Statement stmt = statement();
		      stmt.executeQuery("CREATE TABLE GAST ( DGASTNR NUMBER(2, 0) NOT NULL , DGASTNAME VARCHAR2(20 CHAR) NOT NULL , DEMAIL VARCHAR2(64 CHAR) NOT NULL , DGASTISTSTAMMKUNDE VARCHAR2(5 CHAR) DEFAULT 'FALSE' NOT NULL , CONSTRAINT GAST_PK PRIMARY KEY);");
		      stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void res(){
		try {
		      Statement stmt = statement();
			  stmt.executeQuery("DELETE FROM GAST");
			  stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
